package zy.util;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.lang.StringUtils;

public class ExcelImportUtil {
	
	private static WritableFont wf;
	private static WritableCellFormat rwcfF;
	
	static {
		try {
			wf = new WritableFont(WritableFont.ARIAL, 8, WritableFont.NO_BOLD, false);
			wf.setColour(jxl.format.Colour.RED);
			rwcfF = new WritableCellFormat(wf);
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
	
	public static WritableCellFormat getRwcfF() {
		return rwcfF;
	}
	public static void setRwcfF(WritableCellFormat rwcfF) {
		ExcelImportUtil.rwcfF = rwcfF;
	}
	
	/**  
     * 删除单个文件
     */  
	public static boolean deleteFile(File file) {
	    boolean result = false;
	    if (!file.exists()) {
			return result;
		}
	    int tryCount = 0;
	    while(!result && tryCount++ < 10){
	    	System.gc();
	    	result = file.delete();
	    }
	    return result;
	}
	
	/**
	 * Excel的工作表的实际行数 返回去掉空行的记录数
	 * 
	 * @param sheet
	 * @return
	 */
	public static int getRightRows(Sheet sheet) {
		int rsCols = sheet.getColumns(); // 列数
		int rsRows = sheet.getRows(); // 行数
		int nullCellNum;
		int afterRows = rsRows;
		for (int i = 1; i < rsRows; i++) { // 统计行中为空的单元格数
			nullCellNum = 0;
			for (int j = 0; j < rsCols; j++) {
				String val = sheet.getCell(j, i).getContents();
				val = StringUtils.trimToEmpty(val);
				if (StringUtils.isBlank(val))
					nullCellNum++;
			}
			if (nullCellNum >= rsCols) {
				// 如果nullCellNum大于或等于总的列数
				// 行数减一
				afterRows--;
			}
		}
		return afterRows;
	}
	
	/**
	 * Excel 判断当前行是否是空行
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public static boolean isBlankRow(Sheet sheet, int row, int column) {
		boolean flag = true;
		if (sheet == null || row == 0 /*|| column == 0*/) {
			return flag;
		}
		for (int i = 0; i <= column; i++) {
			String content = sheet.getCell(i, row).getContents().trim();
			if (StringUtils.isNotBlank(content)) {
				flag = false;
				break;
			}
		}
		return flag;
	}
	
	/**
	 * 生成 Excel 错误信息
	 * @param errorSheet
	 * @param sheet
	 * @param nowRow
	 * @param errorRow
	 * @param columns
	 * @param errorCause
	 * @return
	 */
	public static boolean copySheetRow(WritableSheet errorSheet, Sheet sheet, int nowRow, int errorRow, int columns, String errorCause) {
		boolean tag = false;
		Cell cell = null;
		try {
			int i = 0;
			for (; i < columns; i++) {
				cell = sheet.getCell(i, nowRow);
				errorSheet.addCell(new Label(i, errorRow, cell.getContents()));
			}
			errorSheet.addCell(new Label(i, errorRow, errorCause, rwcfF));
			tag = true;
		} catch (RowsExceededException e) {
			tag = false;
		} catch (WriteException e) {
			tag = false;
		}
		return tag;
	}
	
	/**
	 * 正则表达式检测字符串
	 * @param regex
	 * @param str
	 * @return
	 */
	public static boolean checkStrRegex(String regex,String str){
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(str);
		if(matcher.find()){
			return false;
		}else{
			return true;
		}
	}
	
	/**
	 * 获取excel日期 yyyy-MM-dd
	 * @param cell
	 * @return
	 */
	public static String checkCellIsDate(Cell cell){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		try {
			DateCell dc = (DateCell) cell;
			Date date = dc.getDate();
			return format.format(date);
		} catch (Exception e) {
			return "";
		}
	}
	
	public static boolean checkDate(String strDate){
		 try {
			SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
			 Date d = f.parse(strDate);
			 String tmp = f.format(d);
			 if(tmp.equals(strDate)){
				 return true;
			 }else {
				return false;
			}
		} catch (ParseException e) {
			return false;
		}
	}
}
